package com.hava.ttxs.repository;

import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.*;

/**
 * Created by yanfa on 2016/10/9.
 */
public class UserCourseInfoDAO {

    public static BasicDataSource basicDataSource = null;

    public final static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://192.168.1.200/ttxs";
    public final static String USER = "root";
    public final static String PASSWORD = "dVHJtG0T:pf*";

    public void init()
    {
        basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(DB_URL);
        basicDataSource.setDriverClassName(JDBC_DRIVER);
        basicDataSource.setUsername(USER);
        basicDataSource.setPassword(PASSWORD);
    }

    public static void printZhangSanCoursesJDBC() throws ClassNotFoundException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        // 1.添加数据库驱动
        Class.forName(JDBC_DRIVER);

        // 2.创建数据库连接
        try {
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);

            // 3.运行SQL
            String sql = "SELECT CourseName FROM UserCourseInfo WHERE UserName='ZhangSan'";

            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            // 4.读取结果集的内容
            while(resultSet.next())
            {
                String courseName = resultSet.getString("CourseName");
                System.out.println(courseName);
            }
        } catch (SQLException e) {
            // Exception
            e.printStackTrace();
        } finally {
                // 5. close connection
                if(connection != null)
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
            if(statement != null)
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(resultSet != null)
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    public static void printAllByFetch() throws ClassNotFoundException {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        // 1.添加数据库驱动
        Class.forName(JDBC_DRIVER);

        // 2.创建数据库连接
        try {
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);

            // 3.运行SQL
            String sql = "SELECT CourseName,UserName FROM UserCourseInfo";

            statement = connection.prepareStatement(sql);
            statement.setFetchSize(1);
            resultSet = statement.executeQuery();

            // 4.读取结果集的内容
            while(resultSet.next())
            {
                String courseName = resultSet.getString("CourseName");
                String userName = resultSet.getString("UserName");
                System.out.println(courseName + " " + userName);
            }
        } catch (SQLException e) {
            // Exception
            e.printStackTrace();
        } finally {
            // 5. close connection
            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(statement != null)
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(resultSet != null)
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    public static void printZhangSanCoursesDBCP()
    {
        //初始化DBCP
        basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(DB_URL);
        basicDataSource.setDriverClassName(JDBC_DRIVER);
        basicDataSource.setUsername(USER);
        basicDataSource.setPassword(PASSWORD);

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = basicDataSource.getConnection();

            String sql = "SELECT CourseName FROM UserCourseInfo WHERE UserName='ZhangSan'";

            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            // 4.读取结果集的内容
            while(resultSet.next())
            {
                String courseName = resultSet.getString("CourseName");
                System.out.println(courseName);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. close connection
            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(statement != null)
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(resultSet != null)
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    public static void Transaction()
    {
        //初始化DBCP
        basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(DB_URL);
        basicDataSource.setDriverClassName(JDBC_DRIVER);
        basicDataSource.setUsername(USER);
        basicDataSource.setPassword(PASSWORD);

        Connection connection = null;
        PreparedStatement  statement = null;
        ResultSet resultSet = null;
        try {
            connection = basicDataSource.getConnection();
            //开启事务
            connection.setAutoCommit(false);

            //
            String delete_sql = "DELETE FROM UserCourseInfo WHERE UserName = 'ZhangSan' AND CourseName = 'math'";

            String create_sql = "INSERT INTO UserCourseInfo (UserName, CourseName) VALUES ('LiSi', 'math')";

            statement = connection.prepareStatement(delete_sql);
            statement.execute();

            statement = connection.prepareStatement(create_sql);
            statement.execute();

            //提交事务
            connection.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 5. close connection
            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(statement != null)
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(resultSet != null)
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }
}
